﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Logs;
using System.Configuration;

namespace DAL
{
    public class DAL
    {        
        private static string MontarConnString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["csDefault"].ConnectionString;
        }

        public static void ExecuteNonQuery(string sql)
        {
            string connStr = MontarConnString();
            SqlConnection conn = new SqlConnection(connStr);

            //tratar conexão
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro no banco de dados. Det.: " + ex.Message);
                throw;
            }
        }

        public static void ExecSP(string procedure)
        {

        }

        public static void ExecSP(List<SqlParameter> parameters, string procedure)
        {
            //Recuprar QueryString
            string connString = MontarConnString();

            //Instanciar classes e variaveis
            SqlConnection connection = new SqlConnection(connString);
            SqlCommand command = new SqlCommand();

            //Definir a conexão, o tipo de comando e o nome da procedure
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = procedure;

            //Adicionar os parametros da SP
            command.Parameters.AddRange(parameters.ToArray());

            try
            {
                //Abrir a conexão
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro ao executar função - ExecSP. " + ex.Message);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataTable ExecSPWithReturn(string procedure)
        {
            //Recuprar QueryString
            string connString = MontarConnString();

            //Instanciar classes e variaveis
            SqlConnection connection = new SqlConnection(connString);
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            DataTable dt = new DataTable();

            //Definir a conexão, o tipo de comando e o nome da procedure
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = procedure;

            try
            {
                //Abrir a conexão
                connection.Open();

                //Popular e executar a procedure
                adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                //Verificar se tem registros no DataTable
                if (dt.Rows.Count <= 0)
                    dt = null;

                //retornar os valores da SP
                return dt;
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro ao executar função - ExecSPWithReturn. " + ex.Message);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataTable ExecSPWithReturn(List<SqlParameter> parameters, string procedure)
        {
            //Recuprar QueryString
            string connString = MontarConnString();

            //Instanciar classes e variaveis
            SqlConnection connection = new SqlConnection(connString);
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            DataTable dt = new DataTable();

            //Definir a conexão, o tipo de comando e o nome da procedure
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = procedure;

            //Adicionar os parametros da SP
            command.Parameters.AddRange(parameters.ToArray());

            try
            {
                //Abrir a conexão
                connection.Open();

                //Popular e executar a procedure
                adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                //Verificar se tem registros no DataTable
                if (dt.Rows.Count <= 0)
                    dt = null;

                //retornar os valores da SP
                return dt;
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro ao executar função - ExecSPWithReturn. " + ex.Message);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataTable ExecQueryWithReturn(List<SqlParameter> parameters, string query)
        {
            //Recuprar QueryString
            string connString = MontarConnString();

            //Instanciar classes e variaveis
            SqlConnection connection = new SqlConnection(connString);
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            DataTable dt = new DataTable();

            //Definir a conexão, o tipo de comando e o nome da procedure
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = query;

            //Adicionar os parametros da SP
            command.Parameters.AddRange(parameters.ToArray());

            try
            {
                //Abrir a conexão
                connection.Open();

                //Popular e executar a procedure
                adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                //Verificar se tem registros no DataTable
                if (dt.Rows.Count <= 0)
                    dt = null;

                //retornar os valores da SP
                return dt;
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro ao executar função - ExecQueryWithReturn. " + ex.Message);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataTable ExecQueryWithReturn(string query)
        {
            //Recuprar QueryString
            string connString = MontarConnString();

            //Instanciar classes e variaveis
            SqlConnection connection = new SqlConnection(connString);
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            DataTable dt = new DataTable();

            //Definir a conexão, o tipo de comando e o nome da procedure
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = query;

            try
            {
                //Abrir a conexão
                connection.Open();

                //Popular e executar a procedure
                adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);

                //Verificar se tem registros no DataTable
                if (dt.Rows.Count <= 0)
                    dt = null;

                //retornar os valores da SP
                return dt;
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro ao executar função - ExecQueryWithReturn. " + ex.Message);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataTable ReaderDataTable(string sql)
        {
            DataTable dt = new DataTable();
            string connStr = MontarConnString();
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            //tratar conexão
            try
            {
                //Colocar os dados do DataTable
                da.Fill(dt);

                //Verificar se há dados e retorna os valores
                if (dt.Rows.Count > 0)
                    return dt;
                else
                    return null;
            }
            catch (Exception ex)
            {
                Logs.Logs.LogErro("Erro no banco de dados - Func. ReaderDataTable. Det.: " + ex.Message);
                throw;
            }
        }
    }
}

